 /**
 * 
 */
package vanhoc.android.mediakool.db;

import java.util.ArrayList;
import java.util.Currency;
import java.util.HashMap;
import java.util.List;

import vanhoc.android.mediakool.R;
import vanhoc.android.medialcool.item.data.ArtistItemData;
import vanhoc.android.medialcool.item.data.MusicItemData;
import vanhoc.android.medialcool.item.data.PlaylistItemData;
import vanhoc.android.medialcool.item.data.SongItemData;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author Administrator
 *
 */
public class DatabaseHelper {

	public static final int DATABASE_VERSION 	=	1;
	public static final String DATABASE_NAME 	=	"mediaplayer.db";
	
	//song table
	public static final String SONG_TABLE 		=	"songs";
	public static final String SONG_ID			=	"id";
	public static final String SONG_NAME		=	"name";
	public static final String SONG_ALBUM		=	"album";
	public static final String SONG_ARTIST		=	"artist";
	public static final String SONG_GENRE		=	"genre";
	public static final String SONG_YEAR		=	"year";
	public static final String SONG_DURATION	= 	"duration";
	public static final String SONG_PATH		=	"path";
	public static final String ARTIST_THUMB_PATH		=	"thumbPath";
	
	
	//video table
	public static final String VIDEO_TABLE 		=	"videos";
	public static final String VIDEO_ID			=	"id";
	public static final String VIDEO_NAME		=	"name";
	public static final String VIDEO_ARTIST		=	"artist";
	public static final String VIDEO_FORMAT		=	"format";
	public static final String VIDEO_DURATION	= 	"duration";
	public static final String VIDEO_SIZE		=	"size";
	public static final String VIDEO_PATH		=	"path";
	
	
	//playlist table
	public static final String PLAYLIST_TABLE	=	"playlists";
	public static final String PLAYLIST_ID		=	"id";
	public static final String PLAYLIST_TITLE	=	"title";
	
	
	//song_playlist table
	public static final String SONG_PLAYLIST_TABLE	=	"song_playlist";
	public static final String SP_SONG_ID			=	"song_id";
	public static final String SP_PLAYLIST_ID		=	"playlist_id";
	
	
	
	//script create songs table
	private static final String CREATE_SONG_TABLE = "create table " + SONG_TABLE + " ( "
			+ SONG_ID + " integer primary key autoincrement, "
			+ SONG_NAME + " text not null, "
			+ SONG_ALBUM + " text not null, "
			+ SONG_ARTIST + " text not null, "
			+ SONG_GENRE + " text not null, "
			+ SONG_YEAR + " text, "
			+ SONG_DURATION + " text, "
			+ SONG_PATH + " text not null, "
			+ ARTIST_THUMB_PATH + " text " +
			")";
			
	
	//script create videos table
	private static final String CREATE_VIDEO_TABLE = "create table " + VIDEO_TABLE + " ( "
			+ VIDEO_ID + " integer primary key autoincrement, "
			+ VIDEO_NAME + " text not null, "
			+ VIDEO_ARTIST + " text not null, "
			+ VIDEO_FORMAT + " text not null, "
			+ VIDEO_SIZE + " text, "
			+ VIDEO_DURATION + " text, "
			+ SONG_PATH + " text not null )";
	
	
	//script create playlits table
	private static final String CREATE_PLAYLIST_TABLE = "create table " + PLAYLIST_TABLE + " ( "
			+ PLAYLIST_ID + " integer primary key autoincrement, "
			+ PLAYLIST_TITLE + " text not null )";
	
	//script create playlits table
	private static final String CREATE_SONG_PLAYLIST_TABLE = "create table " + SONG_PLAYLIST_TABLE + " ( "
			+ SP_SONG_ID + " text, "
			+ SP_PLAYLIST_ID + " text)";
		
		
	
	private Context dbContext;
	public SQLiteDatabase sqLiteDB;
	public SQLiteMediaHelper sqLiteMediaHelper;
	
	/*Constructor*/
	public DatabaseHelper(Context c){
		dbContext	=	c;
	}
	
	public SQLiteDatabase getDB(){
		return sqLiteDB;
	}
	
	
	/**
	 * open database to read
	 * @return
	 */
	public DatabaseHelper openToRead() throws android.database.SQLException{
		sqLiteMediaHelper = new SQLiteMediaHelper(dbContext, DATABASE_NAME, null, DATABASE_VERSION);
		sqLiteDB	=	sqLiteMediaHelper.getReadableDatabase();
		return this;
	}
	
	
	/**
	 * open database to Write
	 * @return
	 */
	public DatabaseHelper openToWrite() throws android.database.SQLException{
		sqLiteMediaHelper = new SQLiteMediaHelper(dbContext, DATABASE_NAME, null, DATABASE_VERSION);
		sqLiteDB	=	sqLiteMediaHelper.getWritableDatabase();
		return this;
	}
	
	/*Closing database*/
	public void close(){
		sqLiteMediaHelper.close();
	}
	
	
	/*inserting songs data to database*/
	public long insertSong(HashMap<String, String> song){
		ContentValues contentValues = new ContentValues();
		contentValues.put(SONG_NAME, 	song.get(SONG_NAME));
		contentValues.put(SONG_ALBUM, 	song.get(SONG_ALBUM));
		contentValues.put(SONG_ARTIST, 	song.get(SONG_ARTIST));
		contentValues.put(SONG_GENRE, 	song.get(SONG_GENRE));
		contentValues.put(SONG_YEAR, 	song.get(SONG_YEAR));
		contentValues.put(SONG_DURATION,song.get(SONG_DURATION));
		contentValues.put(SONG_PATH, 	song.get(SONG_PATH));
		contentValues.put(ARTIST_THUMB_PATH, 	song.get(ARTIST_THUMB_PATH));
		return sqLiteDB.insert(SONG_TABLE, null, contentValues);
	}
	
	//inserting each video data to database
	public long insertVideo(HashMap<String, String> video) {
		ContentValues values = new ContentValues();
		values.put(VIDEO_NAME, video.get(VIDEO_NAME));
		values.put(VIDEO_ARTIST, video.get(VIDEO_ARTIST));
		values.put(VIDEO_FORMAT, video.get(VIDEO_FORMAT));
		values.put(VIDEO_SIZE, video.get(VIDEO_SIZE));
		values.put(VIDEO_DURATION, video.get(VIDEO_DURATION));
		values.put(VIDEO_PATH, video.get(VIDEO_PATH));
		
		return sqLiteDB.insert(VIDEO_TABLE, null, values);
	}
	
	
	
	/**
	 * Delete all song
	 * @return
	 */
	public int deleteAllSong(){
		return sqLiteDB.delete(SONG_TABLE, null, null);
	}
	
	
	/**
	 * Lay ve day du thong tin tat ca bai hat
	 * @return
	 */
	public Cursor getFullInfoAllSong(){
		String[] columns = new String[]{SONG_ID, SONG_NAME, SONG_ALBUM, SONG_ARTIST, SONG_GENRE,
										SONG_DURATION, SONG_PATH,ARTIST_THUMB_PATH};
		Cursor cursor = sqLiteDB.query(SONG_TABLE, columns, null, null, null, null, null);
		
		return cursor;
	}
	
	/**
	 * Lay ve thong tin cua 1 bai hat 
	 * @param id
	 * @return
	 */
	public Cursor getInfoSong(int id){
		String[] columns = new String[]{SONG_ID, SONG_NAME, SONG_ALBUM, SONG_ARTIST,SONG_DURATION,
										SONG_PATH,SONG_GENRE,ARTIST_THUMB_PATH};
		Cursor cursor = sqLiteDB.query(SONG_TABLE, columns, SONG_ID + " = " + id, null, null, null, null);
		return cursor;
	}
	
	/**
	 * Update title a song
	 * @param songSelectedID
	 * @param newSongTitle
	 */
	public void updateSongTitle(int id, String name) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(SONG_NAME, name);
		
		String where = SONG_ID +"="+id;
		sqLiteDB.update(SONG_TABLE, contentValues, where, null);
	}
	
	//Lay ve 1 thong tin nao do cua tat ca cac bai hat (vi du : lay ve Name cua tat ca cac bai hat)
	public Cursor getSingleInfoAllSong(String field){
		String sql = "select " + field + " from " + SONG_TABLE;
		Cursor cursor = sqLiteDB.rawQuery(sql, null);
		return cursor;
	}
	
	/**
	 * get path of a song
	 * @param songSelectedID
	 * @return
	 */
	public String getSongPath(int songId) {
		String[] column = new String[]{SONG_PATH};
		
		String where =  SONG_ID + " = " + "'"+ songId +"'";
		Cursor cursor = sqLiteDB.query(true, SONG_TABLE, column, where, null, null, null, null, "1");
		cursor.moveToFirst();
		String songPath = cursor.getString(0);

		return songPath;
	}
	
	
	public int deleteSong(int songId){
		return sqLiteDB.delete(SONG_TABLE, SONG_ID +" = "+songId, null);
	}
	
	/*
	 * Lay ve ds tat ca id cua cac bai hat trong sdcard
	 */
	public ArrayList<Integer> getAllSongID(){
		ArrayList<Integer> listItem = new ArrayList<Integer>();
		
		String[] column = new String[]{SONG_ID};
		Cursor cursor = sqLiteDB.query(SONG_TABLE, column, null, null, null, null, null);
		
		cursor.moveToFirst();
 		for (int i = 0; i < cursor.getCount(); i++) {
 			listItem.add(Integer.parseInt(cursor.getString(0)));
 			cursor.moveToNext();
		}
 		
 		return listItem;
	}
	
	
	/**
	 * Lay ve danh sach id cua nhung bai hat thuoc 1 field (trong songs table) voi dk theo fieldvalue
	 * @param _fieldName
	 * @param _fieldValue
	 * @return
	 */
	public ArrayList<Integer> getListSongIdByFieldName(String fieldName, String fieldValue) {
		ArrayList<Integer> arrItems = new ArrayList<Integer>();
		
		String[] column = new String[]{SONG_ID};
		Cursor cursor = sqLiteDB.query(SONG_TABLE, column, fieldName + " = " + "'"+fieldValue+"'", null, null, null, null);
		cursor.moveToFirst();
 		for (int i = 0; i < cursor.getCount(); i++) {
 			arrItems.add(Integer.parseInt(cursor.getString(0)));
 			cursor.moveToNext();
 		}
 		
 		return arrItems;
	}
	
	/**
	 * Update the path of artist thumbnail
	 * @param _songIDPlay
	 * @param path
	 */
	/*public void updateArtistThumbnailPath(int sid, String path) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(ARTIST_THUMB_PATH, path);
		
		String where = SONG_ID +"="+sid;
		sqLiteDB.update(SONG_TABLE, contentValues, where, null);
	}*/
	public void updateArtistThumbnailPath(String artistName, String path) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(ARTIST_THUMB_PATH, path);
		String where =  SONG_ARTIST + " = " + "'"+ artistName +"'";
		sqLiteDB.update(SONG_TABLE, contentValues, where, null);
	}
	
	
	
	public Cursor getSongID(String artist) {
		String[] column = new String[]{SONG_ID};
		String where =  SONG_ARTIST + " = " + "'"+ artist +"'";
		return sqLiteDB.query(SONG_TABLE, column, where, null, null, null, null);
	}
	
	
	
	public Cursor getListSongIdOfPlaylist(int currentSongID){
		String[] column = new String[]{SONG_ID};
		//lay ve playlist_id cua currentSongID
		int currPlaylist =0;
		Cursor cursor = sqLiteDB.query(SONG_TABLE, column, null, null, null, null, null);
		return cursor;
	}
	
	
	
	//Lay ve danh sach cac title cua cac album/genre/artist trong bang songs
	public List<String> getListTitleByField(String fieldname){
		List<String> listData = new ArrayList<String>();
		String selectQuery = "select distinct " + fieldname + " from " + SONG_TABLE;
		Cursor cur = sqLiteDB.rawQuery(selectQuery, null);
		
		cur.moveToFirst();
		for (int i = 0; i < cur.getCount(); i++) {
			listData.add(cur.getString(0));
			cur.moveToNext();
		}
		
		return listData;
	}
	
	
	
	/*Lay ve danh sach cac AlbumItemData (ten album + so luong bai hat trong album)  hoac cac GenreItemData(title + number songs)*/
	public ArrayList<MusicItemData> getAllItemDataByFieldName(String fieldname){
		ArrayList<MusicItemData> arrListItemData = new ArrayList<MusicItemData>();
		
		//Lay ve title cua cac fieldname(co the la album hoac genre) trong db
		List<String> listFileTitle = getListTitleByField(fieldname);
		
		for (int i = 0; i < listFileTitle.size(); i++) {
			MusicItemData itemData = new MusicItemData();
			
			//Lay ve ten cua moi album
			String itemTitle = listFileTitle.get(i);
			
			//truy van csdl de xem voi ten album nay thi co bao nhieu bai hat
			String[] columns = new String[]{SONG_ID};
			Cursor cursor = sqLiteDB.query(SONG_TABLE, columns, fieldname + " = " + "'"+itemTitle+"'", null, null, null, null);
			int numSong = cursor.getCount();
			
			//get desc for album
			String itemDesc =  String.valueOf(numSong) + " songs";
			itemData.setTitle(itemTitle);
			itemData.setDescription(itemDesc);
			//adding to list
			arrListItemData.add(itemData);
		}
		
		return arrListItemData;
	}
	
	
	/**
	 * Lay ve danh sach cac bai hat cua 1 album hay 1 artist hoac 1 genre hoac 1 playlist
	 * @param fieldName
	 * @param value
	 * @return
	 */
	public ArrayList<SongItemData> getSongListByTagName(String fieldName, String value){
		ArrayList<SongItemData> listSongData = new ArrayList<SongItemData>();
		
		//truy van csdl de xem voi ten album nay thi co bao nhieu bai hat
		String[] columns = new String[]{SONG_ID, SONG_NAME, SONG_ALBUM, SONG_ARTIST, SONG_GENRE,
										SONG_DURATION, SONG_PATH };
		
		Cursor cursor = sqLiteDB.query(SONG_TABLE, columns, fieldName + " = " + "'"+ value +"'", null, null, null, null);
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {
			SongItemData item = new SongItemData();
			item.setID(Integer.parseInt(cursor.getString(0)));
			item.setName(cursor.getString(1));
			item.setAlbum(cursor.getString(2));
			item.setArtist(cursor.getString(3));
			item.setGenre(cursor.getString(4));
			item.setDuration(cursor.getString(5));
			item.setPath(cursor.getString(6));
			
			listSongData.add(item);
			cursor.moveToNext();
		}
		
		return listSongData;
	}
	 
	
	/**
	 * Get artist name by list
	 * @return
	 */
	public ArrayList<String> getArtistNameList() {
		ArrayList<String> arlistList = new ArrayList<String>();
		
		String selectQuery = "select distinct " + SONG_ARTIST + " from " + SONG_TABLE ;
		Cursor cursor = sqLiteDB.rawQuery(selectQuery, null);
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {
			arlistList.add(cursor.getString(0).toString());
			cursor.moveToNext();
		}		
		return arlistList;
	}
	
	
	/**
	 * Lay ve 1 mang cac hashmap chua ten ca si va path den hinh dai dien
	 * @return
	 */
	public  Cursor getArtistItemList(){
		String[] columns = new String[]{SONG_ARTIST, ARTIST_THUMB_PATH};
		return sqLiteDB.query(true, SONG_TABLE, columns, null, null, null, null, null, null);
	}
	
	
	
	
	
	
	//get all video to put to listView
	public Cursor getAllVideo(){
		String[] columns = new String[]{VIDEO_ID, VIDEO_NAME, VIDEO_ARTIST, VIDEO_FORMAT,VIDEO_SIZE,VIDEO_DURATION, VIDEO_PATH};
		Cursor cursor = sqLiteDB.query(VIDEO_TABLE, columns,null, null, null, null, null);
		return cursor;
	}
	
	
	/********Lam viec voi bang playlist***********/
	
	/**
	 * Xoa 1 bai hat khoi 1 playlist
	 * @param sid
	 * @param pid
	 */
	public int removeSongFromPlaylist(int sid, int pid) {
		String where = SP_SONG_ID+ " = '"+sid+"'"
			    + " AND "+SP_PLAYLIST_ID+" = '"+pid+"'";
		return sqLiteDB.delete(SONG_PLAYLIST_TABLE, where, null);
	}
	
	
	
	/**
	 * add 1 playlist
	 */
	public long addPlaylist(String title){
		ContentValues contentValues = new ContentValues();
		contentValues.put(PLAYLIST_TITLE, 	title);
		return sqLiteDB.insert(PLAYLIST_TABLE, null, contentValues);
	}
	
	
	
	
	/**
	 * Lay ve danh sach cac playlist tu csdl
	 */
	
	public ArrayList<PlaylistItemData> getAllPlaylist(){
		ArrayList<PlaylistItemData> arrListPlaylist = new ArrayList<PlaylistItemData>();
		String[] columns = new String[]{PLAYLIST_ID, PLAYLIST_TITLE};
		Cursor cursor = sqLiteDB.query(PLAYLIST_TABLE, columns, null, null, null, null, null);
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {
			PlaylistItemData playlist = new PlaylistItemData();
			playlist.setPlaylistId(Integer.parseInt(cursor.getString(0)));
			playlist.setPlaylistTitle(cursor.getString(1));
			
			arrListPlaylist.add(playlist);
			cursor.moveToNext();
		}
		
		return arrListPlaylist;
	}
	
	
	/**
	 * Lay ve ds nhac theo 1 playlist nao do
	 * @param pid
	 * @return
	 */
	public Cursor getListSongByPlaylist(int pid){
		//truy van csdl de xem voi ten album nay thi co bao nhieu bai hat
		String sql = "SELECT distinct s."+SONG_ID+","+ SONG_NAME+"," + SONG_ALBUM+"," + SONG_ARTIST+"," + SONG_GENRE+"," + SONG_DURATION+"," + SONG_PATH+
						" FROM " +SONG_TABLE+ " as s " 
						+ "left join "+ SONG_PLAYLIST_TABLE+" as sp ON s."+SONG_ID+" = sp."+ SP_SONG_ID
						+ " where " + SP_PLAYLIST_ID + " = " + pid;
		Log.e("sql get list song by pid ", sql);
		
		Cursor cursor = sqLiteDB.rawQuery(sql, null);
		return cursor;
	}
	
	/**
	 * 
	 * @param pid
	 */
	public int deletePlaylistItem(int pid) {
		return sqLiteDB.delete(PLAYLIST_TABLE, PLAYLIST_ID +" = "+pid, null);
	}
	
	
	/***********Lam viec voi bang song_playlist********/
	 
	
	/**
	 * Them 1 record moi
	 * @param sid : id cua bai hat
	 * @param pid : id cua playlist
	 */
	public long insertSongToPlaylist(int sid, int pid){
		ContentValues contentValues = new ContentValues();
		contentValues.put(SP_SONG_ID, 	sid);
		contentValues.put(SP_PLAYLIST_ID, 	pid);
		return sqLiteDB.insert(SONG_PLAYLIST_TABLE, null, contentValues);
	}
	
	/**
	 * Lay ve id cua playlist cuoi cung trong bang playlist (tuc la id cua playlist moi dc tao) -> add 1 song vao 1 playlist moi dc tao
	 * @return
	 */
	public int getLastPlaylistId(){
		String[] columns = new String[]{PLAYLIST_ID};
		Cursor cursor = sqLiteDB.query(PLAYLIST_TABLE, columns, null, null, null, null, null, "1");
		cursor.moveToLast();
		int pid = Integer.parseInt(cursor.getString(0));
		
		return pid;
	}
	
	
	/**
	 * Lay ve danh sach cac id bai hat thuoc playlist co id la pid
	 * @param pid
	 * @return
	 */
	public ArrayList<Integer> getSongListIdByPlaylist(int pid){
		ArrayList<Integer> arrListSongId = new ArrayList<Integer>();
		String[] column = new String[]{SP_SONG_ID};
		String where =  SP_PLAYLIST_ID + " = " + "'"+ pid +"'";
		Cursor cursor = sqLiteDB.query(true, SONG_PLAYLIST_TABLE, column, where, null, null, null, null, null);
		cursor.moveToFirst();
		for (int i = 0; i <cursor.getCount(); i++) {
			arrListSongId.add(Integer.parseInt(cursor.getString(0)));
			cursor.moveToNext();
		}
		
		return arrListSongId;
	}
	
	
	/**
	 * update title a playlist item
	 * @param pid
	 * @param title
	 */
	public void updatePlaylistItem(int pid, String title) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(PLAYLIST_TITLE, title);
		
		String where = PLAYLIST_ID +"="+pid;
		sqLiteDB.update(PLAYLIST_TABLE, contentValues, where, null);
	}
	
	
	public int deleteDataPlaylist(int pid){
		String where =   SP_PLAYLIST_ID +" = "+ pid;
		return sqLiteDB.delete(SONG_PLAYLIST_TABLE, where, null);
	}
	
	/**
	 * Create SQLiteMediaHelper Class extend SQLiteOpenHelper
	 * @author Administrator
	 *
	 */
	class SQLiteMediaHelper extends SQLiteOpenHelper{

		public SQLiteMediaHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(CREATE_SONG_TABLE);
			db.execSQL(CREATE_VIDEO_TABLE);
			db.execSQL(CREATE_PLAYLIST_TABLE);
			db.execSQL(CREATE_SONG_PLAYLIST_TABLE);
			
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + SONG_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + CREATE_VIDEO_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + CREATE_PLAYLIST_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + CREATE_SONG_PLAYLIST_TABLE);
			
			onCreate(db);
		}
	}//End SQLiteMediaHelper Class

}
